package dao;

import classes.Lotacao;
import conexao.GerenciadorConexao;
import erro.ConexaoException;
import erro.ConsultaException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;

/**
 *
 * @author Roberta
 */
public class LotacaoDao {

    private GerenciadorConexao g;

    public LotacaoDao() {
        g = GerenciadorConexao.getInstancia();
    }

    /**
     * 
     * @param l = Lotacao
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public void inserir(Lotacao l) throws ConexaoException, ConsultaException {
        String sql = "INSERT INTO lotacao (sigla,nome) VALUES (?,?)";
        try {
            PreparedStatement pstm = (PreparedStatement) g.connect().prepareStatement(sql);
            pstm.setString(1, l.getSigla());
            pstm.setString(2, l.getNome());
            pstm.executeUpdate();

        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
    }

    /**
     * 
     * @return
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public List<Lotacao> listar() throws ConexaoException, ConsultaException {
        List<Lotacao> lista = new ArrayList<Lotacao>();
        Lotacao l;
        String sql = "SELECT * FROM lotacao ORDER BY sigla";
        try {
            Statement stm = (Statement) g.connect().createStatement();
            ResultSet rs = stm.executeQuery(sql);
            while (rs.next()) {
                l = new Lotacao();
                l.setLotacao_id(rs.getInt("idlotacao"));
                l.setNome(rs.getString("nome"));
                l.setSigla(rs.getString("sigla"));
                lista.add(l);
            }
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
        return lista;
    }

    /**
     * 
     * @param l = Lotacao
     * @return
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public Lotacao pesquisar(Lotacao l) throws ConexaoException, ConsultaException {
        String sql = "SELECT * FROM lotacao WHERE idlotacao=?";
        try {
            PreparedStatement pstm = g.connect().prepareStatement(sql);
            pstm.setInt(1, l.getLotacao_id());
            ResultSet rs = pstm.executeQuery();
            if (rs.next()) {
                l.setLotacao_id(rs.getInt("idlotacao"));
                l.setNome(rs.getString("nome"));
                l.setSigla(rs.getString("sigla"));
            }
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
        return l;
    }

    /**
     * 
     * @param l = Lotacao
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public void deletar(Lotacao l) throws ConexaoException, ConsultaException {

        String sql = "DELETE FROM lotacao WHERE idlotacao=?";
        try {
            PreparedStatement pstm = (PreparedStatement) g.connect().prepareStatement(sql);
            pstm.setInt(1, l.getLotacao_id());
            pstm.executeUpdate();

        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, "Lotação em uso!");
        } finally {
            g.disconnect();
        }
    }

    /**
     * 
     * @param l = Lotacao
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public void alterarLot(Lotacao l) throws ConexaoException, ConsultaException {
        String sql = "UPDATE lotacao SET sigla=?,nome=? WHERE idlotacao=?";
        try {
            PreparedStatement pstm = (PreparedStatement) g.connect().prepareStatement(sql);
            pstm.setString(1, l.getSigla());
            pstm.setString(2, l.getNome());

            pstm.setInt(3, l.getLotacao_id());
            pstm.executeUpdate();
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
    }
}
